const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const bcrypt = require('bcrypt');
const fs = require('fs');

// 数据库路径
const DB_PATH = path.join(__dirname, '../data/bbs.db');

// 确保数据目录存在
const ensureDataDir = () => {
  const dataDir = path.join(__dirname, '../data');
  if (!fs.existsSync(dataDir)) {
    fs.mkdirSync(dataDir, { recursive: true });
  }
};

// 创建数据库连接
const createDbConnection = () => {
  ensureDataDir();
  return new sqlite3.Database(DB_PATH, (err) => {
    if (err) {
      console.error('无法连接到数据库:', err.message);
      process.exit(1);
    }
    console.log('已连接到 SQLite 数据库');
  });
};

// 数据库连接实例
let db;

// 初始化数据库
const initDb = () => {
  db = createDbConnection();

  // 创建用户表
  db.run(`CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,
    email TEXT UNIQUE,
    avatar TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  )`);

  // 创建帖子表
  db.run(`CREATE TABLE IF NOT EXISTS posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    user_id INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(user_id) REFERENCES users(id)
  )`);

  // 创建回帖表
  db.run(`CREATE TABLE IF NOT EXISTS comments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    content TEXT NOT NULL,
    post_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(post_id) REFERENCES posts(id),
    FOREIGN KEY(user_id) REFERENCES users(id)
  )`);

  // 创建文件表
  db.run(`CREATE TABLE IF NOT EXISTS files (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    filename TEXT NOT NULL,
    original_name TEXT NOT NULL,
    path TEXT NOT NULL,
    mime_type TEXT,
    size INTEGER,
    user_id INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(user_id) REFERENCES users(id)
  )`);

  // 创建消息表
  db.run(`CREATE TABLE IF NOT EXISTS messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    sender_id INTEGER NOT NULL,
    receiver_id INTEGER,
    room_id TEXT,
    content TEXT NOT NULL,
    is_group BOOLEAN DEFAULT 0,
    is_read BOOLEAN DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(sender_id) REFERENCES users(id),
    FOREIGN KEY(receiver_id) REFERENCES users(id)
  )`);

  // 创建房间表
  db.run(`CREATE TABLE IF NOT EXISTS rooms (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    password TEXT,
    is_private BOOLEAN DEFAULT 0,
    creator_id INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(creator_id) REFERENCES users(id)
  )`);

  // 创建房间成员表
  db.run(`CREATE TABLE IF NOT EXISTS room_members (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    room_id TEXT NOT NULL,
    user_id INTEGER NOT NULL,
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_admin BOOLEAN DEFAULT 0,
    FOREIGN KEY(room_id) REFERENCES rooms(id),
    FOREIGN KEY(user_id) REFERENCES users(id),
    UNIQUE(room_id, user_id)
  )`);
};

// 用户相关操作
const createUser = (userData) => {
  return new Promise(async (resolve, reject) => {
    try {
      const { username, password, email } = userData;
      const hashedPassword = await bcrypt.hash(password, 10);

      db.run(
        'INSERT INTO users (username, password, email) VALUES (?, ?, ?)',
        [username, hashedPassword, email],
        function (err) {
          if (err) {
            reject(err);
            return;
          }
          resolve({ id: this.lastID, username, email });
        }
      );
    } catch (error) {
      reject(error);
    }
  });
};

const getUserByUsername = (username) => {
  return new Promise((resolve, reject) => {
    db.get('SELECT * FROM users WHERE username = ?', [username], (err, row) => {
      if (err) {
        reject(err);
        return;
      }
      resolve(row);
    });
  });
};

const getUserById = (id) => {
  return new Promise((resolve, reject) => {
    db.get('SELECT id, username, email, avatar, created_at FROM users WHERE id = ?', [id], (err, row) => {
      if (err) {
        reject(err);
        return;
      }
      resolve(row);
    });
  });
};

// 帖子相关操作
const createPost = (postData) => {
  return new Promise((resolve, reject) => {
    const { title, content, userId } = postData;

    db.run(
      'INSERT INTO posts (title, content, user_id) VALUES (?, ?, ?)',
      [title, content, userId],
      function (err) {
        if (err) {
          reject(err);
          return;
        }
        resolve({ id: this.lastID, title, content, userId });
      }
    );
  });
};

const getPosts = (page = 1, limit = 10) => {
  return new Promise((resolve, reject) => {
    const offset = (page - 1) * limit;

    db.all(
      `SELECT p.*, u.username 
       FROM posts p 
       JOIN users u ON p.user_id = u.id 
       ORDER BY p.created_at DESC 
       LIMIT ? OFFSET ?`,
      [limit, offset],
      (err, rows) => {
        if (err) {
          reject(err);
          return;
        }

        db.get('SELECT COUNT(*) as total FROM posts', [], (err, count) => {
          if (err) {
            reject(err);
            return;
          }
          resolve({
            posts: rows,
            total: count.total,
            page,
            limit,
            pages: Math.ceil(count.total / limit)
          });
        });
      }
    );
  });
};

const getPostById = (postId) => {
  return new Promise((resolve, reject) => {
    db.get(
      `SELECT p.*, u.username 
       FROM posts p 
       JOIN users u ON p.user_id = u.id 
       WHERE p.id = ?`,
      [postId],
      (err, post) => {
        if (err) {
          reject(err);
          return;
        }

        if (!post) {
          resolve(null);
          return;
        }

        // 获取帖子的评论
        db.all(
          `SELECT c.*, u.username 
           FROM comments c 
           JOIN users u ON c.user_id = u.id 
           WHERE c.post_id = ? 
           ORDER BY c.created_at ASC`,
          [postId],
          (err, comments) => {
            if (err) {
              reject(err);
              return;
            }
            post.comments = comments;
            resolve(post);
          }
        );
      }
    );
  });
};

// 评论相关操作
const createComment = (commentData) => {
  return new Promise((resolve, reject) => {
    const { content, postId, userId } = commentData;

    db.run(
      'INSERT INTO comments (content, post_id, user_id) VALUES (?, ?, ?)',
      [content, postId, userId],
      function (err) {
        if (err) {
          reject(err);
          return;
        }
        resolve({ id: this.lastID, content, postId, userId });
      }
    );
  });
};

// 文件相关操作
const saveFile = (fileData) => {
  return new Promise((resolve, reject) => {
    const { filename, originalName, path, mimeType, size, userId } = fileData;

    db.run(
      'INSERT INTO files (filename, original_name, path, mime_type, size, user_id) VALUES (?, ?, ?, ?, ?, ?)',
      [filename, originalName, path, mimeType, size, userId],
      function (err) {
        if (err) {
          reject(err);
          return;
        }
        resolve({ id: this.lastID, filename, originalName, path });
      }
    );
  });
};

const getFileById = (fileId) => {
  return new Promise((resolve, reject) => {
    db.get('SELECT * FROM files WHERE id = ?', [fileId], (err, file) => {
      if (err) {
        reject(err);
        return;
      }
      resolve(file);
    });
  });
};

const getFilesByUser = (userId) => {
  return new Promise((resolve, reject) => {
    db.all('SELECT * FROM files WHERE user_id = ? ORDER BY created_at DESC', [userId], (err, files) => {
      if (err) {
        reject(err);
        return;
      }
      resolve(files);
    });
  });
};

const deleteFile = (fileId) => {
  return new Promise((resolve, reject) => {
    db.run('DELETE FROM files WHERE id = ?', [fileId], function (err) {
      if (err) {
        reject(err);
        return;
      }
      resolve({ success: true, deleted: this.changes });
    });
  });
};

// 消息相关操作
const saveMessage = (messageData) => {
  return new Promise((resolve, reject) => {
    const { sender_id, receiver_id, room_id, content, is_group, created_at } = messageData;

    db.run(
      'INSERT INTO messages (sender_id, receiver_id, room_id, content, is_group, created_at) VALUES (?, ?, ?, ?, ?, ?)',
      [sender_id, receiver_id, room_id, content, is_group ? 1 : 0, created_at],
      function (err) {
        if (err) {
          reject(err);
          return;
        }
        resolve({ id: this.lastID, ...messageData });
      }
    );
  });
};

const getMessages = (userId, targetId, isGroup = false, limit = 50) => {
  return new Promise((resolve, reject) => {
    let query, params;

    if (isGroup) {
      // 获取群组消息
      query = `SELECT m.*, u.username 
               FROM messages m 
               JOIN users u ON m.sender_id = u.id 
               WHERE m.room_id = ? AND m.is_group = 1 
               ORDER BY m.created_at DESC 
               LIMIT ?`;
      params = [targetId, limit];
    } else {
      // 获取私聊消息
      query = `SELECT m.*, u.username 
               FROM messages m 
               JOIN users u ON m.sender_id = u.id 
               WHERE (m.sender_id = ? AND m.receiver_id = ?) 
                  OR (m.sender_id = ? AND m.receiver_id = ?) 
               ORDER BY m.created_at DESC 
               LIMIT ?`;
      params = [userId, targetId, targetId, userId, limit];
    }

    db.all(query, params, (err, rows) => {
      if (err) {
        reject(err);
        return;
      }
      resolve(rows.reverse()); // 反转数组以获得按时间正序排列的消息
    });
  });
};

// 房间相关操作
const createRoom = (roomData) => {
  return new Promise(async (resolve, reject) => {
    try {
      const { id, name, description, password, is_private, creator_id } = roomData;

      // 如果有密码，进行哈希处理
      let hashedPassword = null;
      if (password) {
        hashedPassword = await bcrypt.hash(password, 10);
      }

      db.run(
        'INSERT INTO rooms (id, name, description, password, is_private, creator_id) VALUES (?, ?, ?, ?, ?, ?)',
        [id, name, description, hashedPassword, is_private ? 1 : 0, creator_id],
        function (err) {
          if (err) {
            reject(err);
            return;
          }

          // 自动将创建者添加为房间管理员
          db.run(
            'INSERT INTO room_members (room_id, user_id, is_admin) VALUES (?, ?, 1)',
            [id, creator_id],
            (err) => {
              if (err) {
                reject(err);
                return;
              }
              resolve({ id, name, description, is_private, creator_id });
            }
          );
        }
      );
    } catch (error) {
      reject(error);
    }
  });
};

const getRooms = (userId) => {
  return new Promise((resolve, reject) => {
    // 如果用户未登录，只返回公共房间
    if (!userId) {
      db.all(
        `SELECT r.*, u.username as creator_name,
                (SELECT COUNT(*) FROM room_members WHERE room_id = r.id) as member_count
         FROM rooms r 
         JOIN users u ON r.creator_id = u.id 
         WHERE r.is_private = 0
         ORDER BY r.created_at DESC`,
        [],
        (err, rows) => {
          if (err) {
            reject(err);
            return;
          }
          resolve(rows);
        }
      );
    } else {
      // 用户已登录，返回公共房间 + 用户已加入的私有房间
      db.all(
        `SELECT r.*, u.username as creator_name,
                rm.is_admin,
                (SELECT COUNT(*) FROM room_members WHERE room_id = r.id) as member_count
         FROM rooms r 
         JOIN users u ON r.creator_id = u.id 
         LEFT JOIN room_members rm ON r.id = rm.room_id AND rm.user_id = ?
         WHERE r.is_private = 0 OR rm.user_id IS NOT NULL
         ORDER BY r.created_at DESC`,
        [userId],
        (err, rows) => {
          if (err) {
            reject(err);
            return;
          }
          resolve(rows);
        }
      );
    }
  });
};

const getRoomById = (roomId) => {
  return new Promise((resolve, reject) => {
    db.get(
      `SELECT r.*, u.username as creator_name 
       FROM rooms r 
       JOIN users u ON r.creator_id = u.id 
       WHERE r.id = ?`,
      [roomId],
      (err, room) => {
        if (err) {
          reject(err);
          return;
        }
        resolve(room);
      }
    );
  });
};

const joinRoom = (roomId, userId, password = null) => {
  return new Promise(async (resolve, reject) => {
    try {
      // 首先获取房间信息
      const room = await getRoomById(roomId);
      if (!room) {
        reject(new Error('房间不存在'));
        return;
      }

      // 检查用户是否已经在房间中
      db.get(
        'SELECT * FROM room_members WHERE room_id = ? AND user_id = ?',
        [roomId, userId],
        async (err, member) => {
          if (err) {
            reject(err);
            return;
          }

          if (member) {
            resolve({ success: true, message: '已在房间中' });
            return;
          }

          // 如果房间有密码，验证密码
          if (room.password) {
            if (!password) {
              reject(new Error('需要密码'));
              return;
            }

            const isPasswordValid = await bcrypt.compare(password, room.password);
            if (!isPasswordValid) {
              reject(new Error('密码错误'));
              return;
            }
          }

          // 添加用户到房间
          db.run(
            'INSERT INTO room_members (room_id, user_id) VALUES (?, ?)',
            [roomId, userId],
            function (err) {
              if (err) {
                reject(err);
                return;
              }
              resolve({ success: true, message: '成功加入房间' });
            }
          );
        }
      );
    } catch (error) {
      reject(error);
    }
  });
};

const leaveRoom = (roomId, userId) => {
  return new Promise((resolve, reject) => {
    db.run(
      'DELETE FROM room_members WHERE room_id = ? AND user_id = ?',
      [roomId, userId],
      function (err) {
        if (err) {
          reject(err);
          return;
        }
        resolve({ success: true, deleted: this.changes });
      }
    );
  });
};

const deleteRoom = (roomId, userId) => {
  return new Promise((resolve, reject) => {
    // 首先检查用户是否是房间创建者
    db.get(
      'SELECT * FROM rooms WHERE id = ? AND creator_id = ?',
      [roomId, userId],
      (err, room) => {
        if (err) {
          reject(err);
          return;
        }

        if (!room) {
          reject(new Error('无权限删除此房间'));
          return;
        }

        // 开始事务删除房间及相关数据
        db.serialize(() => {
          db.run('BEGIN TRANSACTION');

          // 删除房间成员
          db.run('DELETE FROM room_members WHERE room_id = ?', [roomId]);

          // 删除房间消息
          db.run('DELETE FROM messages WHERE room_id = ? AND is_group = 1', [roomId]);

          // 删除房间
          db.run('DELETE FROM rooms WHERE id = ?', [roomId], function (err) {
            if (err) {
              db.run('ROLLBACK');
              reject(err);
              return;
            }

            db.run('COMMIT');
            resolve({ success: true, deleted: this.changes });
          });
        });
      }
    );
  });
};

const getRoomMembers = (roomId) => {
  return new Promise((resolve, reject) => {
    db.all(
      `SELECT u.id, u.username, rm.is_admin, rm.joined_at
       FROM room_members rm
       JOIN users u ON rm.user_id = u.id
       WHERE rm.room_id = ?
       ORDER BY rm.is_admin DESC, rm.joined_at ASC`,
      [roomId],
      (err, rows) => {
        if (err) {
          reject(err);
          return;
        }
        resolve(rows);
      }
    );
  });
};

// 导出模块
module.exports = {
  initDb,
  createUser,
  getUserByUsername,
  getUserById,
  createPost,
  getPosts,
  getPostById,
  createComment,
  saveFile,
  getFileById,
  getFilesByUser,
  deleteFile,
  saveMessage,
  getMessages,
  createRoom,
  getRooms,
  getRoomById,
  joinRoom,
  leaveRoom,
  deleteRoom,
  getRoomMembers
}; 